package vn.edu.saigontech.ncms.service.impl;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import vn.edu.saigontech.ncms.model.info.AbstractReceipt;
import vn.edu.saigontech.ncms.model.info.Offering;
import vn.edu.saigontech.ncms.model.info.OfferingMember;
import vn.edu.saigontech.ncms.model.info.Receipt;
import vn.edu.saigontech.ncms.model.info.Student;
import vn.edu.saigontech.ncms.model.program.Program;
import vn.edu.saigontech.ncms.service.OfferingMemberService;
import vn.edu.saigontech.ncms.service.OfferingService;
import vn.edu.saigontech.ncms.service.ReceiptService;
import vn.edu.saigontech.ncms.service.StudentService;
import vn.edu.saigontech.ncms.util.Checker;
import vn.edu.saigontech.ncms.util.DateUtil;

@SuppressWarnings("unchecked")
public class StudentServiceImpl extends HibernateGenericService<Student, Long>
		implements StudentService {

	@Override
	public boolean deleteById(Long id) {
		if (!ReceiptService.INSTANCE.findByStudentId(id).isEmpty())
			return false;
		return super.deleteById(id);
	}

	@Override
	public Collection<Student> find(Student student) {
		Criteria studentCriteria = this.createCriteria();
		if (!Checker.isEmpty(student.getFirstName())) {
			studentCriteria.add(Restrictions.ilike("firstName", student
					.getFirstName(), MatchMode.ANYWHERE));
		}
		if (!Checker.isEmpty(student.getLastName())) {
			studentCriteria.add(Restrictions.ilike("lastName", student
					.getLastName(), MatchMode.ANYWHERE));
		}
		if (!Checker.isEmpty(student.getNickName())) {
			studentCriteria.add(Restrictions.ilike("nickName", student
					.getNickName(), MatchMode.ANYWHERE));
		}
		if (!Checker.isNull(student.getGender())) {
			studentCriteria.add(Restrictions.eq("gender", student.getGender()));
		}
		if (!Checker.isNull(student.getDateOfBirth())) {
			studentCriteria.add(Restrictions.eq("dateOfBirth", student
					.getDateOfBirth()));
		}
		if (!Checker.isEmpty(student.getPhoneNumber())) {
			studentCriteria.add(Restrictions.ilike("phoneNumber", student
					.getPhoneNumber(), MatchMode.ANYWHERE));
		}
		if (!Checker.isEmpty(student.getEmail())) {
			studentCriteria.add(Restrictions.ilike("email", student.getEmail(),
					MatchMode.ANYWHERE));
		}
		if (!Checker.isEmpty(student.getAddress())) {
			studentCriteria.add(Restrictions.ilike("address", student
					.getAddress(), MatchMode.ANYWHERE));
		}
		if (!Checker.isNull(student.getType())) {
			studentCriteria.add(Restrictions.eq("type", student.getType()));
		}
		if(!Checker.isNull(student.getScholarship())) {
			studentCriteria.add(Restrictions.eq("scholarship", student.getScholarship()));
		}
		
		/**/
		if(!Checker.isNull(student.getAdditionalInfo())) {
			studentCriteria.createCriteria("additionalInfo").add(Example.create(student.getAdditionalInfo()));
			
		}
		/**/
		
		if(Checker.isNotNull(student.getPlacementShift())) {
			studentCriteria.add(Restrictions.eq("placementShift", student.getPlacementShift()));
		}
		if (!Checker.isEmpty(student.getComment())) {
			studentCriteria.add(Restrictions.ilike("comment", student
					.getComment(), MatchMode.ANYWHERE));
		}
		if (!Checker.isNull(student.getConsultant())) {
			studentCriteria.createCriteria("consultant").add(
					Restrictions.eq("id", student.getConsultant().getId()));
		}
		if (Checker.isNotNull(student.getFromConsultedDate())
				&& Checker.isNotNull(student.getToConsultedDate())) {
			studentCriteria.add(Restrictions.between("consultedDate", student
					.getFromConsultedDate(), student.getToConsultedDate()));
		} else if (!Checker.isNull(student.getConsultedDate())) {
			studentCriteria.add(Restrictions.eq("consultedDate", student
					.getConsultedDate()));
		}
		if (!Checker.isNull(student.getProgram())) {
			studentCriteria.createCriteria("program").add(
					Restrictions.eq("id", student.getProgram().getId()));
		}
		if (!Checker.isNull(student.getTestDate())) {
			studentCriteria.add(Restrictions.eq("testDate", student
					.getTestDate()));
		}
		studentCriteria.addOrder(Order.asc("firstName"));
		studentCriteria.addOrder(Order.asc("lastName"));
		return studentCriteria.list();
	}

	public Collection<Student> findRecently() {
		Criteria criteria = this.createCriteria();
		criteria.addOrder(Order.desc("id"));
		criteria.setMaxResults(5);
		return criteria.list();
	}

	@Override
	public void merge(Long[] ids, Long id) {
		Student student = this.findById(id);
		for (int i = 0; i < ids.length; i++) {
			if (ids[i].equals(id))
				continue;
			Student each = this.findById(ids[i]);
			for (AbstractReceipt receipt : each.getOfferingReceipts()) {
				receipt.setStudent(student);
			}
			this.getSession().delete(each);
		}
	}

	@Override
	public Map<Student, List<AbstractReceipt>> findStudy(Date dateFrom, Date dateTo, boolean includeInactive) {
		Collection<Offering> offerings = OfferingService.INSTANCE.findByDate(
				dateFrom, dateTo);
		Map<Student, List<AbstractReceipt>> map = new HashMap<Student, List<AbstractReceipt>>();
		for (Offering offering : offerings) {
			for (AbstractReceipt receipt : offering.getOfferingReceipts()) {
				if (!includeInactive && !receipt.getOfferingMember().isActive())
					continue;
				Student key = receipt.getStudent();
				if (map.get(key) == null) {
					List<AbstractReceipt> value = new ArrayList<AbstractReceipt>();
					value.add(receipt);
					map.put(key, value);
				} else {
					map.get(key).add(receipt);
				}
			}
		}
		return map;
	}
	
	@Override
	public Map<Student, List<AbstractReceipt>> findUnStudy(Date dateFrom, Date dateTo) {
		Collection<Offering> offerings = OfferingService.INSTANCE.findByDate(
				dateFrom, dateTo);
		Map<Student, List<AbstractReceipt>> map = new HashMap<Student, List<AbstractReceipt>>();
		for (Offering offering : offerings) {
			for (AbstractReceipt receipt : offering.getOfferingReceipts()) {
				if (receipt.getOfferingMember().isActive())
					continue;
				Student key = receipt.getStudent();
				if (map.get(key) == null) {
					List<AbstractReceipt> value = new ArrayList<AbstractReceipt>();
					value.add(receipt);
					map.put(key, value);
				} else {
					map.get(key).add(receipt);
				}
			}
		}
		return map;
	}

	@Override
	public Map<Student, List<AbstractReceipt>> findByBirthDay(int month) {
		Collection<OfferingMember> members = OfferingMemberService.INSTANCE
				.findByBirthDay(month);
		Map<Student, List<AbstractReceipt>> ret = new HashMap<Student, List<AbstractReceipt>>();
		for (OfferingMember member : members) {
			Receipt receipt = member.getReceipt();
			if (!member.isActive())
				continue;
			Student key = receipt.getStudent();
			if (ret.get(key) == null) {
				List<AbstractReceipt> value = new ArrayList<AbstractReceipt>();
				value.add(receipt);
				ret.put(key, value);
			} else
				ret.get(key).add(receipt);
		}
		return ret;
	}
	
	@Override
	public Map<Student, List<AbstractReceipt>> findByBirthDayAndProgram(int month, long programId) {
		Collection<OfferingMember> members = OfferingMemberService.INSTANCE
				.findByBirthDayAndProgram(month, programId);
		Map<Student, List<AbstractReceipt>> ret = new HashMap<Student, List<AbstractReceipt>>();
		for (OfferingMember member : members) {
			Receipt receipt = member.getReceipt();
			if (!member.isActive())
				continue;
			Student key = receipt.getStudent();
			if (ret.get(key) == null) {
				List<AbstractReceipt> value = new ArrayList<AbstractReceipt>();
				value.add(receipt);
				ret.put(key, value);
			} else
				ret.get(key).add(receipt);
		}
		return ret;
	}

	@Override
	public List<Student> findByMonth(int month, int year) {
		Date startDate = DateUtil.getStartDateOfMonth(month, year);
		Date endDate = DateUtil.getEndDateOfMonth(month, year);
		
		Criteria criteria = this.createCriteria();
		criteria.add(Restrictions.between("consultedDate", startDate, endDate));
		return criteria.list();
	}

	@Override
	public int findByProgram(int month, int year, Program program) {
		Date startDate = DateUtil.getStartDateOfMonth(month, year);
		Date endDate = DateUtil.getEndDateOfMonth(month, year);
		
		Criteria criteria = this.createCriteria();
		criteria.add(Restrictions.between("consultedDate", startDate, endDate));
		criteria.createCriteria("program").add(Restrictions.eq("id", program.getId()));
		return criteria.list().size();
	}
	
	
	/* halnt */
	@Override
	public Object[] findByDateNProgram(int month, int year, Program program) {
		Date startDate = DateUtil.getStartDateOfMonth(month, year);
		Date endDate = DateUtil.getEndDateOfMonth(month, year);
		
		String sStr = DateUtil.toString(startDate, "yyyy-MM-dd");
		String eStr = DateUtil.toString(endDate, "yyyy-MM-dd");
		
		String queryString = "select e.pname, a.na, b.nb, c.nc, d.nd "+
			"from " +
			"(select count(id) as na from ncms_student " +
			"where consulted_date between '" + sStr + "'  and '" + eStr + "' and program_id=" + program.getId() +") a, " +
			"(select count(a.id) as nb from ncms_student a, ncms_placement_test b " +
			"where b.date between '" + sStr + "' and '" + eStr + "' and a.id = b.student_id and program_id=" + program.getId() +") b, " +
			"(select count(a.id) as nc from ncms_student a, ncms_receipt b, ncms_offering c, ncms_level d, ncms_program e " +
			"where a.id = b.student_id and a.consulted_date between '" + sStr + "' and '" + eStr + "' and b.date between '" + sStr + "' and '" + eStr + "' and b.offering_id = c.id and c.level_id = d.id and d.program_id = e.id and e.id=" + program.getId() +") c, " +
			"(select count(a.id) as nd from ncms_waiting a, ncms_level b, ncms_program c, ncms_student d " +
			"where a.student_id = d.id and a.registered_date between '" + sStr + "' and '" + eStr + "' and d.consulted_date between '" + sStr + "' and '" + eStr + "' and a.level_id = b.id and b.program_id = c.id and c.id=" + program.getId() +") d," +
			"(select name as pname from ncms_program where id=" + program.getId() +") e";
		
		SQLQuery query = getSession().createSQLQuery(queryString);
		return (Object[])query.list().get(0);
	}
	
	@Override
	public Object[] findFreeTuitionByDateNTuition(int month, int year, Long studentTypeId) {
		Date startDate = DateUtil.getStartDateOfMonth(month, year);
		Date endDate = DateUtil.getEndDateOfMonth(month, year);
		
		String sStr = DateUtil.toString(startDate, "yyyy-MM-dd");
		String eStr = DateUtil.toString(endDate, "yyyy-MM-dd");
		
		String queryString = "select a.nStudents, b.nPlacementTests, c.nRegisters, d.nWaitings, e.nStudyings from " + 
			"(select count(a.id) as nStudents from ncms_student a " +
			"where a.consulted_date between '" + sStr + "' and '" + eStr + "' and a.type_id = " + studentTypeId + ") a," +
			"(select count(a.id) as nPlacementTests from ncms_student a, ncms_placement_test b " +
			"where b.student_id = a.id " +
			"and b.date between '" + sStr + "' and '" + eStr + "' and a.type_id = " + studentTypeId + ") b," +
			"(select count(a.id) as nRegisters from ncms_student a, ncms_receipt b " +
			"where b.student_id = a.id " +
			"and b.date between '" + sStr + "' and '" + eStr + "' and a.type_id = " + studentTypeId + ") c," +
			"(select count(a.id) as nWaitings from ncms_student a, ncms_waiting b " +
			"where b.student_id = a.id " +
			"and b.registered_date between '" + sStr + "' and '" + eStr + "' and a.type_id = " + studentTypeId + ") d," +
			"(select count(a.id) as nStudyings from ncms_student a, ncms_offering_member b, ncms_receipt c, ncms_offering d " +
			"where b.offering_receipt_id = c.id and c.student_id = a.id and c.offering_id = d.id " +
			"and (d.end_date between '" + sStr + "' and '" + eStr + "' or " +
			"start_date between '" + sStr + "' and '" + eStr + "') and a.type_id = " + studentTypeId + ") e";
		
		SQLQuery query = getSession().createSQLQuery(queryString);
		return (Object[])query.list().get(0);
	}

	@Override
	public Collection<Student> findAllRecently() {
		String queryString = "SELECT * FROM ncms_student WHERE " +
				"extract(month from consulted_date) in (extract(month from current_date)-1, extract(month from current_date)) " +
				"AND extract(year from consulted_date) = extract(year from current_date)";
		
		SQLQuery query = getSession().createSQLQuery(queryString);
		query.addEntity(Student.class);
		return query.list();
	}

	@Override
	public Collection<Student> findAllRecentlyByDate() {
		String queryString = "SELECT * FROM ncms_student WHERE " +
				"extract(month from consulted_date) in (extract(month from current_date)-1, extract(month from current_date)) " +
				"AND extract(year from consulted_date) = extract(year from current_date) " + 
				"ORDER BY consulted_date DESC, first_name ASC";
		
		SQLQuery query = getSession().createSQLQuery(queryString);
		query.addEntity(Student.class);
		return query.list();
	}

	@Override
	public Collection<Student> findByDateAndProgram(Long programId, Date from, Date to, boolean oddDay) {

		String queryString = "SELECT DISTINCT stu.* FROM ncms_offering AS o, ncms_receipt AS re, ncms_offering_member AS mem, " +
				"ncms_program AS pro, ncms_level AS lv, ncms_student AS stu " +
				"WHERE o.id = re.offering_id AND re.id = mem.offering_receipt_id " +
				"AND o.level_id = LV.id AND  lv.program_id = pro.id AND stu.id = re.student_id " +
				"AND o.end_date > ? AND o.start_date < ? " +
				"AND o.cancel = FALSE AND mem.active = 't' " +
				"AND pro.id = ? AND o.mon_teacher " + (oddDay ? "ISNULL" : "NOTNULL"); 
		
		SQLQuery query = getSession().createSQLQuery(queryString);
		query.setDate(0, from);
		query.setDate(1, to);
		query.setLong(2, programId);
		query.addEntity(Student.class);
		return query.list();
	}

	@Override
	public Collection<Student> findByConsultDate(Date from, Date to) {
		Criteria criteria = this.createCriteria();
		criteria.add(Restrictions.between("consultedDate", from, to));
		criteria.addOrder(Order.asc("firstName"));
		criteria.addOrder(Order.asc("lastName"));
		return criteria.list();
	}
	
	/*find
	SELECT count(DISTINCT stu.id) FROM ncms_offering AS o, ncms_receipt AS re, ncms_offering_member AS mem, 
	ncms_program AS pro, ncms_level AS lv, ncms_student AS stu
WHERE o.id = re.offering_id AND re.id = mem.offering_receipt_id
	AND o.level_id = LV.id AND  lv.program_id = pro.id AND stu.id = re.student_id
	AND o.end_date > '2010/11/01' AND o.start_date < '2010/11/30' 
	AND o.cancel = FALSE AND mem.active = 't'
	AND pro.id = '13'*/
}